const { BaseDatabase, getDbPath } = require('./db');

class UserModel extends BaseDatabase {
	constructor() {
		super(getDbPath('tiktok_data'));
		this.tableName = 'users';
		this._initTable();
	}

	// 初始化用户表结构
	_initTable() {
		this.exec(`
			CREATE TABLE IF NOT EXISTS user_profile (
				id TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
				display_id TEXT UNIQUE, -- 用户展示ID
				anchor_id TEXT, -- 主播id（用作查询金币榜单）
				room_id TEXT, -- 房间ID（用作查询金币榜单）
				sec_uid TEXT, -- 用户唯一标识
				region TEXT, -- 区域
				country TEXT, -- 国家
				source TEXT, -- 数据来源
				coins TEXT, -- 金币
				followers TEXT, -- 粉丝数量
				following TEXT, -- 关注数量
				audience TEXT, -- 观众数量
				type TEXT, -- 用户类型（主播/普通用户）
				tags TEXT, -- 标签（逗号分隔）
				created_at TEXT, -- 数据创建时间
				updated_at TEXT, -- 最后更新时间
				verified_time TEXT, -- 数据查验时间
				verification_result TEXT, -- 查验结果
				uploaded_at TEXT -- 数据上传时间
			);`);
	}
	// 获取 country 每个值的数量（包括空值）
	async getCountryCounts() {
		const sql = `
        SELECT 
            country, 
            COUNT(*) AS count 
        FROM user_profile 
        GROUP BY country
    `;

		try {
			const stmt = this.db.prepare(sql);
			const results = stmt.all();
			return results; // 返回 [{ country: 'US', count: 10 }, { country: null, count: 5 }, ...]
		} catch (error) {
			console.error('Database query error:', error);
			throw error; // 抛出错误以便调用方处理
		}
	}
	// 批量插入
	async batchInsert(users) {
		const columns = ['display_id', 'room_id', 'anchor_id', 'sec_uid', 'followers', 'following', 'audience', 'tags', 'source', 'created_at', 'updated_at', 'coins'];

		// 确保 display_id 有唯一约束
		const insertSql = `
		  INSERT INTO user_profile (${columns.join(', ')}) 
		  VALUES (${columns.map(() => '?').join(', ')})
		  ON CONFLICT (display_id) DO UPDATE SET 
			room_id = excluded.room_id,
			anchor_id = excluded.anchor_id,
			sec_uid = excluded.sec_uid,
			followers = excluded.followers,
			following = excluded.following,
			audience = excluded.audience,
			tags = excluded.tags,
			source = excluded.source,
			updated_at = excluded.updated_at,
			coins = excluded.coins
		`;

		return this.transaction(() => {
			const stmt = this.db.prepare(insertSql);
			users.forEach((user) => {
				const values = columns.map((col) => user[col] ?? null);
				stmt.run(...values);
			});
		});
	}

	// 查询未验证的用户
	async getUnverified(mcn, limit = 30) {
		let mcns = mcn.split('|');
		const placeholders = mcns.map(() => `country LIKE ?`).join(' OR '); // 动态生成每个国家的 LIKE 条件
		const sql = `
			SELECT * FROM user_profile
			WHERE verification_result IS NULL
			AND (${placeholders})
			AND country IS NOT NULL
			AND coins IS NOT NULL
			LIMIT ?
		`;
		const stmt = this.db.prepare(sql);
		const parameters = mcns.map(mcn => `%${mcn}%`); // 为每个国家添加通配符，进行 LIKE 匹配
		parameters.push(limit); // 将 limit 参数添加到最后
		return stmt.all(...parameters);
	}

	// 清空表中的所有数据
	async clearAll() {
		const sql = `
            DELETE FROM user_profile;
        `;

		return this.transaction(() => {
			this.db.exec(sql);
			console.log('表 user_profile 已清空');
		});
	}

	// 获取国家或金币为空的
	async getCountyOrCoinsNull() {
		const sql = `
			SELECT * FROM user_profile 
			WHERE country IS NULL OR coins IS NULL
		  `;
		const stmt = this.db.prepare(sql);
		return stmt.all();
	}

	// 批量更新
	async batchUpdateById(ids, updateData) {
		const setClauses = Object.keys(updateData)
			.map((key) => `${key} = ?`)
			.join(', ');
		const values = Object.values(updateData);

		const placeholders = ids.map(() => '?').join(', ');
		values.push(...ids);

		const sql = `
			UPDATE user_profile 
			SET ${setClauses}
			WHERE id IN (${placeholders})
		  `;

		return this.transaction(() => {
			const stmt = this.db.prepare(sql);
			return stmt.run(...values);
		});
	}

	// 筛选+分页查询
	async searchAll(search, page = 1, pageSize = 10) {
		// 定义字段的类型和操作符映射
		const fieldTypes = {
			display_id: 'string', // 字符串类型，使用模糊搜索
			region: 'string',
			country: 'string',
			source: 'string',
			tags: 'string',
			verification_result: 'string',
			coins: 'number', // 数值类型，使用大于条件
			followers: 'number',
			following: 'number',
			audience: 'number',
		};

		// 构造 WHERE 子句
		const conditions = [];
		const values = [];

		for (const [key, value] of Object.entries(search)) {
			if (value !== undefined && value !== null && value !== '') {
				if (fieldTypes[key] === 'string') {
					// 字符串类型：使用模糊搜索
					conditions.push(`${key} LIKE ?`);
					values.push(`%${value}%`); // 模糊匹配
				} else if (fieldTypes[key] === 'number') {
					// 数值类型：使用大于条件
					conditions.push(`${key} > ?`);
					values.push(Number(value)); // 转换为数字
				}
			}
		}

		const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';
		const limitClause = `LIMIT ${pageSize} OFFSET ${pageSize * (page - 1)}`;

		// 动态表名支持（可选）
		const tableName = 'user_profile'; // 可以通过参数传入表名

		// 构造 SQL 查询
		const sql = `
			SELECT * 
			FROM ${tableName}
			${whereClause}
			${limitClause}
		`;

		// 执行查询
		try {
			const stmt = this.db.prepare(sql);
			return stmt.all(...values);
		} catch (error) {
			console.error('Database query error:', error);
			throw error; // 抛出错误以便调用方处理
		}
	}
	// 查询总记录数
	async countAll(filters, tableName = 'user_profile') {
		// 构造 WHERE 子句
		const conditions = [];
		const values = [];

		for (const [key, value] of Object.entries(filters)) {
			if (value !== undefined && value !== null && value !== '') {
				conditions.push(`${key} = ?`);
				values.push(value);
			}
		}

		const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

		// 动态表名支持
		const sql = `
			SELECT COUNT(*) AS total 
			FROM ${tableName}
			${whereClause}
		`;

		// 执行查询
		try {
			const stmt = this.db.prepare(sql);
			const result = stmt.get(...values);
			return result.total; // 返回总数
		} catch (error) {
			console.error('Database query error:', error);
			throw error; // 抛出错误以便调用方处理
		}
	}

	// 批量删除
	async batchDelete(ids) {
		const placeholders = ids.map(() => '?').join(', ');
		const sql = `
			DELETE FROM user_profile 
			WHERE id IN (${placeholders})
		  `;
		const stmt = this.db.prepare(sql);
		return stmt.run(...ids);
	}

	transaction(callback) {
		this.db.exec('BEGIN TRANSACTION;');
		try {
			callback();
			this.db.exec('COMMIT;');
		} catch (error) {
			this.db.exec('ROLLBACK;');
			console.error('事务错误:', error);
			throw error;
		}
	}
}

module.exports = UserModel;
